﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;
using System.Configuration;
using System.Collections.ObjectModel;

namespace PSTS.Models
{
    /// <summary>
    /// Class which provides interface for working with database
    /// Implemented pattern: Singleton
    /// </summary>
    public class Repository
    {
        /// <summary>
        /// connection string to database
        /// </summary>
        private string _connectionString;

        /// <summary>
        /// implementation of "singleton" pattern
        /// </summary>
        #region Singleton
        private static Repository _instance = null;

        /// <summary>
        /// Default constructor
        /// Check if db exists, create it if it doesn`t exists
        /// </summary>
        private Repository()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder(_connectionString);
            string baseName = builder.DataSource;

            if (!System.IO.File.Exists(baseName))  //Create db if it doesn`t exists
            {
                SQLiteConnection.CreateFile(baseName);
                
                //Create tables for new db
                using (SQLiteConnection db = new SQLiteConnection(_connectionString))
                {
                    db.Open();
                    const string sqlString = "PRAGMA foreign_keys = ON;";
                    SQLiteCommand cmd = new SQLiteCommand(sqlString, db);
                    cmd.ExecuteNonQuery();
                    
                    //Create chapters table
                    using (SQLiteCommand command = new SQLiteCommand(db))
                    {
                        command.CommandText = @"CREATE TABLE [chapters] ( [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [name] char(300) NOT NULL);";
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
                    //Create topics table
                    using (SQLiteCommand command = new SQLiteCommand(db))
                    {
                        command.CommandText = @"CREATE TABLE [topics] ( [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [name] char(300) NOT NULL, [chapterId] int NOT NULL, FOREIGN KEY(chapterId) REFERENCES chapters(id) ON DELETE CASCADE ON UPDATE CASCADE);";
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
                    //Create topics table
                    using (SQLiteCommand command = new SQLiteCommand(db))
                    {
                        command.CommandText = @"CREATE TABLE [questions] ( [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [text] char(1000) NOT NULL, [correctAnsIndex] int NOT NULL, [topicId] int NOT NULL, FOREIGN KEY(topicId) REFERENCES topics(id) ON DELETE CASCADE ON UPDATE CASCADE);";
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
                    //Create answers table
                    using (SQLiteCommand command = new SQLiteCommand(db))
                    {
                        command.CommandText = @"CREATE TABLE [answers] ( [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [text] char(1000) NOT NULL, [questionId] int NOT NULL, FOREIGN KEY(questionId) REFERENCES questions(id) ON DELETE CASCADE ON UPDATE CASCADE);";
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
                    db.Close();
                }
            }
        }

        /// <summary>
        /// The only instance for repository
        /// </summary>
        public static Repository Instance
        {
            get
            {
                if (_instance == null)
                    _instance = new Repository();
                return _instance;
            }
        }
        #endregion //Singleton

        #region HelperMethods

        /// <summary>
        /// Non query request to database
        /// </summary>
        /// <param name="sql">The SQL to be run</param>
        /// <returns>An Integer containing the number of rows updated</returns>
        private int ExecuteNonQuery(string sql)
        {
            int rowsUpdated = 0;
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                const string sqlString = "PRAGMA foreign_keys = ON;";
                SQLiteCommand command = new SQLiteCommand(sqlString, connection);
                command.ExecuteNonQuery();

                SQLiteCommand mycommand = new SQLiteCommand(connection);
                mycommand.CommandText = sql;
                rowsUpdated = mycommand.ExecuteNonQuery();
                connection.Close();
            }
            return rowsUpdated;
        }

        /// <summary>
        ///  Request to get a single item from database
        /// </summary>
        /// <param name="sql">The query to run</param>
        /// <returns>A string result.</returns>
        private string ExecuteScalar(string sql)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                const string sqlString = "PRAGMA foreign_keys = ON;";
                SQLiteCommand command = new SQLiteCommand(sqlString, connection);
                command.ExecuteNonQuery();

                SQLiteCommand mycommand = new SQLiteCommand(connection);
                mycommand.CommandText = sql;
                object value = mycommand.ExecuteScalar();
                connection.Close();
                if (value != null)
                {
                    return value.ToString();
                }
            }
            return "";
        }

        /// <summary>
        /// Query to get some data from database
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <returns>A DataTable containing the result set.</returns>
        public DataTable GetData(string sql)
        {
            DataTable dt = new DataTable();
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                const string sqlString = "PRAGMA foreign_keys = ON;";
                SQLiteCommand command = new SQLiteCommand(sqlString, connection);
                command.ExecuteNonQuery();

                SQLiteCommand mycommand = new SQLiteCommand(connection);
                mycommand.CommandText = sql;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                connection.Close();
            }
            return dt;
        }

        #endregion //HelperMethods

        #region Chapters

        /// <summary>
        /// Add chapter to database
        /// </summary>
        /// <param name="name">Name of chapter to add</param>
        public void AddChapter(string name)
        {
            if (this.HasChapter(name))
                throw new Exception("Database allready has chapter with such name.");
            string queryString = String.Format("INSERT INTO chapters (name) VALUES ('{0}');",name.Replace('\'','`'));
            this.ExecuteNonQuery(queryString);
        }

        /// <summary>
        /// Update existed chapter by id
        /// </summary>
        /// <param name="chapter">Chapter data for update</param>
        public void UpdateChapter(Chapter chapter)
        {
            string queryString = String.Format("UPDATE chapters SET name='{0}' WHERE id={1};", chapter.Name.Replace('\'', '`'), chapter.Id);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no chapters to update.");
        }

        /// <summary>
        /// Check if specified chapter exists in database
        /// </summary>
        /// <param name="id">Id of specified chapter</param>
        /// <returns>Bool value that indicates chapter existing</returns>
        public bool HasChapter(int id)
        {
            string queryString = String.Format("SELECT COUNT(name) FROM chapters WHERE id = {0};",id);
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Check if specified chapter exists in database
        /// </summary>
        /// <param name="name">Name of specified chapter</param>
        /// <returns>Bool value that indicates chapter existing</returns>
        public bool HasChapter(string name)
        {
            string queryString = String.Format("SELECT COUNT(id) FROM chapters WHERE name = '{0}';", name.Replace('\'', '`'));
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Check if specified chapter exists in database
        /// </summary>
        /// <param name="chapter">Specified chapter</param>
        /// <returns>Bool value that indicates chapter existing</returns>
        public bool HasChapter(Chapter chapter)
        {
            string queryString = String.Format("SELECT COUNT(id) FROM chapters WHERE id = {0} AND name = '{1}';", chapter.Id, chapter.Name.Replace('\'', '`'));
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Remove specified chapter
        /// </summary>
        /// <param name="id">Id of chapter to remove</param>
        public void DeleteChapter(int id)
        {
            string queryString = String.Format("DELETE FROM chapters WHERE id = {0};", id);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no chapters to delete.");
        }

        /// <summary>
        /// Remove specified chapter
        /// </summary>
        /// <param name="name">Name of chapter to remove</param>
        public void DeleteChapter(string name)
        {
            string queryString = String.Format("DELETE FROM chapters WHERE name = '{0}';", name.Replace('\'', '`'));
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no chapters to delete.");
        }

        /// <summary>
        /// Remove specified chapter
        /// </summary>
        /// <param name="chapter">Chapter to remove</param>
        public void DeleteChapter(Chapter chapter)
        {
            string queryString = String.Format("DELETE FROM chapters WHERE id = {0} AND name = '{1}';", chapter.Id, chapter.Name.Replace('\'', '`'));
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no chapters to delete.");
        }

        /// <summary>
        /// Get specified chapter
        /// </summary>
        /// <param name="id">Id of specified chapter</param>
        /// <returns>Finded chapter</returns>
        public Chapter GetChapter(int id)
        {
            Chapter toRet = new Chapter();
            string queryString = String.Format("SELECT * FROM chapters WHERE id = {0};", id);
            try
            {
                DataTable dt = this.GetData(queryString);
                DataRow row = dt.Rows[0];
                toRet.Id = int.Parse(row["id"].ToString());
                toRet.Name = row["name"].ToString();
            }
            catch(Exception ex)
            {
                throw new Exception("There are no chapter with such id.");
            }
            return toRet;
        }

        /// <summary>
        /// Get specified chapter
        /// </summary>
        /// <param name="name">Name of specified chapter</param>
        /// <returns>Finded chapter</returns>
        public Chapter GetChapter(string name)
        {
            Chapter toRet = new Chapter();
            string queryString = String.Format("SELECT * FROM chapters WHERE name = '{0}';", name.Replace('\'', '`'));
            try
            {
                DataTable dt = this.GetData(queryString);
                DataRow row = dt.Rows[0];
                toRet.Id = int.Parse(row["id"].ToString());
                toRet.Name = row["name"].ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("There are no chapter with such name.");
            }
            return toRet;
        }

        /// <summary>
        /// Get all chapters from database
        /// </summary>
        /// <returns>List of chapters</returns>
        public List<Chapter> GetAllChaptersList()
        {
            List<Chapter> toRet = new List<Chapter>();
            string queryString = "SELECT * FROM chapters;";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Chapter ch = new Chapter();
                ch.Id = int.Parse(row["id"].ToString());
                ch.Name = row["name"].ToString();
                toRet.Add(ch);
            }
            return toRet;
        }

        /// <summary>
        /// Get all chapters from database
        /// </summary>
        /// <returns>ObservableCollection of chapters</returns>
        public ObservableCollection<Chapter> GetAllChaptersCollection()
        {
            ObservableCollection<Chapter> toRet = new ObservableCollection<Chapter>();
            string queryString = "SELECT * FROM chapters;";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Chapter ch = new Chapter();
                ch.Id = int.Parse(row["id"].ToString());
                ch.Name = row["name"].ToString();
                toRet.Add(ch);
            }
            return toRet;
        }

        /// <summary>
        /// Find all chapters which name contains specifying string
        /// </summary>
        /// <param name="contains">String for searching </param>
        /// <returns>List of finded chapters</returns>
        public List<Chapter> FindChaptersList(string contains)
        {
            List<Chapter> toRet = new List<Chapter>();
            string queryString = "SELECT * FROM chapters WHERE name LIKE '%" + contains.Replace('\'', '`') + "%';";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Chapter ch = new Chapter();
                ch.Id = int.Parse(row["id"].ToString());
                ch.Name = row["name"].ToString();
                toRet.Add(ch);
            }
            return toRet;
        }

        /// <summary>
        /// Find all chapters which name contains specifying string
        /// </summary>
        /// <param name="contains">String for searching </param>
        /// <returns>Collection of finded chapters</returns>
        public ObservableCollection<Chapter> FindChaptersCollection(string contains)
        {
            ObservableCollection<Chapter> toRet = new ObservableCollection<Chapter>();
            string queryString = "SELECT * FROM chapters WHERE name LIKE '%" + contains.Replace('\'', '`') + "%';";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Chapter ch = new Chapter();
                ch.Id = int.Parse(row["id"].ToString());
                ch.Name = row["name"].ToString();
                toRet.Add(ch);
            }
            return toRet;
        }

        #endregion //Chapters

        #region Topics

        /// <summary>
        /// Add topic to database
        /// </summary>
        /// <param name="name">Name of topic to add</param>
        /// <param name="chapterId">Id of fathers chapter</param>
        public void AddTopic(string name, int chapterId)
        {
            if (!this.HasChapter(chapterId))
                throw new Exception("There are no specified chapters finded");
            if (this.HasTopic(name) && this.GetTopic(name).ChapterId == chapterId)
                throw new Exception("Database allready has topic with such name and chapterId.");
            string queryString = String.Format("INSERT INTO topics (name, chapterId) VALUES ('{0}', {1});", name.Replace('\'', '`'), chapterId);
            this.ExecuteNonQuery(queryString);
        }

        /// <summary>
        /// Update existed topic by id
        /// </summary>
        /// <param name="chapter">Topic data for update</param>
        public void UpdateTopic(Topic topic)
        {
            string queryString = String.Format("UPDATE topics SET name='{0}', chapterId={1} WHERE id={2};", topic.Name.Replace('\'', '`'), topic.ChapterId, topic.Id);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no topics to update.");
        }

        /// <summary>
        /// Check if specified topic exists in database
        /// </summary>
        /// <param name="id">Id of specified topic</param>
        /// <returns>Bool value that indicates topic existing</returns>
        public bool HasTopic(int id)
        {
            string queryString = String.Format("SELECT COUNT(name) FROM topics WHERE id = {0};", id);
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Check if specified topic exists in database
        /// </summary>
        /// <param name="name">Name of specified topic</param>
        /// <returns>Bool value that indicates topic existing</returns>
        public bool HasTopic(string name)
        {
            string queryString = String.Format("SELECT COUNT(id) FROM topics WHERE name = '{0}';", name.Replace('\'', '`'));
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Check if specified topic exists in database
        /// </summary>
        /// <param name="topic">Specified topic</param>
        /// <returns>Bool value that indicates topic existing</returns>
        public bool HasTopic(Topic topic)
        {
            string queryString = String.Format("SELECT COUNT(id) FROM topics WHERE id = {0} AND name = '{1}' AND chapterId = {2};", topic.Id, topic.Name.Replace('\'', '`'), topic.ChapterId);
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Check if specified topic exists in database
        /// </summary>
        /// <param name="topic">Specified topic</param>
        /// <returns>Bool value that indicates topic existing</returns>
        public bool HasTopic(string name, int chapterId)
        {
            string queryString = String.Format("SELECT COUNT(id) FROM topics WHERE name = '{0}' AND chapterId = {1};", name.Replace('\'', '`'), chapterId);
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Remove specified Topic
        /// </summary>
        /// <param name="id">Id of topic to remove</param>
        public void DeleteTopic(int id)
        {
            string queryString = String.Format("DELETE FROM topics WHERE id = {0};", id);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no topics to delete.");
        }

        /// <summary>
        /// Remove specified topic
        /// </summary>
        /// <param name="name">Name of topic to remove</param>
        public void DeleteTopic(string name)
        {
            string queryString = String.Format("DELETE FROM topics WHERE name = '{0}';", name.Replace('\'', '`'));
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no topics to delete.");
        }

        /// <summary>
        /// Remove specified topic
        /// </summary>
        /// <param name="chapter">Topic to remove</param>
        public void DeleteTopic(Topic topic)
        {
            string queryString = String.Format("DELETE FROM topics WHERE id = {0} AND name = '{1}' AND chapterId = {2};", topic.Id, topic.Name.Replace('\'', '`'), topic.ChapterId);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no topics to delete.");
        }

        /// <summary>
        /// Remove all topics of specified chapter
        /// </summary>
        /// <param name="id">Id of specified chapter</param>
        public void DeleteAllChapterTopics(int chapterId)
        {
            string queryString = String.Format("DELETE FROM topics WHERE chapterId = {0};", chapterId);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no topics to delete.");
        }

        /// <summary>
        /// Remove all topics of specified chapter
        /// </summary>
        /// <param name="chapter">Id of specified chapter</param>
        public void DeleteAllChapterTopics(Chapter chapter)
        {
            string queryString = String.Format("DELETE FROM topics WHERE chapterId = {0};", chapter.Id);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no topics to delete.");
        }

        /// <summary>
        /// Remove all topics of specified chapter
        /// </summary>
        /// <param name="chapterName">Name of specified chapter</param>
        public void DeleteAllChapterTopics(string chapterName)
        {
            int chapterId = this.GetChapter(chapterName).Id;
            string queryString = String.Format("DELETE FROM topics WHERE chapterId = {0};", chapterId);
            int rowsChanged = this.ExecuteNonQuery(queryString);
            if (rowsChanged == 0)
                throw new Exception("There are no topics to delete.");
        }

        /// <summary>
        /// Get specified topic
        /// </summary>
        /// <param name="id">Id of specified topic</param>
        /// <returns>Finded topic</returns>
        public Topic GetTopic(int id)
        {
            Topic toRet = new Topic();
            string queryString = String.Format("SELECT * FROM topics WHERE id = {0};", id);
            try
            {
                DataTable dt = this.GetData(queryString);
                DataRow row = dt.Rows[0];
                toRet.Id = int.Parse(row["id"].ToString());
                toRet.Name = row["name"].ToString();
                toRet.ChapterId = int.Parse(row["chapterId"].ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("There are no topic with such id.");
            }
            return toRet;
        }

        /// <summary>
        /// Get specified topic
        /// </summary>
        /// <param name="name">Name of specified topic</param>
        /// <returns>Finded topic</returns>
        public Topic GetTopic(string name)
        {
            Topic toRet = new Topic();
            string queryString = String.Format("SELECT * FROM topics WHERE name = '{0}';", name.Replace('\'', '`'));
            try
            {
                DataTable dt = this.GetData(queryString);
                DataRow row = dt.Rows[0];
                toRet.Id = int.Parse(row["id"].ToString());
                toRet.Name = row["name"].ToString();
                toRet.ChapterId = int.Parse(row["chapterId"].ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("There are no topic with such name.");
            }
            return toRet;
        }

        /// <summary>
        /// Get all chapter topics
        /// </summary>
        /// <param name="chapterId">Id of specified chapter</param>
        /// <returns>List of finded topics</returns>
        public List<Topic> GetChapterTopicsList(int chapterId)
        {
            List<Topic> toRet = new List<Topic>();
            string queryString = String.Format("SELECT * FROM topics WHERE chapterId = '{0}';", chapterId);
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Topic topic = new Topic();
                topic.Id = int.Parse(row["id"].ToString());
                topic.Name = row["name"].ToString();
                topic.ChapterId = int.Parse(row["chapterId"].ToString());
                toRet.Add(topic);
            }
            return toRet;
        }

        /// <summary>
        /// Get all chapter topics
        /// </summary>
        /// <param name="chapterId">Id of specified chapter</param>
        /// <returns>List of finded topics</returns>
        public ObservableCollection<Topic> GetChapterTopicsCollection(int chapterId)
        {
            ObservableCollection<Topic> toRet = new ObservableCollection<Topic>();
            string queryString = String.Format("SELECT * FROM topics WHERE chapterId = '{0}';", chapterId);
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Topic topic = new Topic();
                topic.Id = int.Parse(row["id"].ToString());
                topic.Name = row["name"].ToString();
                topic.ChapterId = int.Parse(row["chapterId"].ToString());
                toRet.Add(topic);
            }
            return toRet;
        }

        /// <summary>
        /// Get all topics from database
        /// </summary>
        /// <returns>List of topics</returns>
        public List<Topic> GetAllTopicsList()
        {
            List<Topic> toRet = new List<Topic>();
            string queryString = "SELECT * FROM topics;";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Topic tp = new Topic();
                tp.Id = int.Parse(row["id"].ToString());
                tp.Name = row["name"].ToString();
                tp.ChapterId = int.Parse(row["chapterId"].ToString());
                toRet.Add(tp);
            }
            return toRet;
        }

        /// <summary>
        /// Get all topics from database
        /// </summary>
        /// <returns>ObservableCollection of topics</returns>
        public ObservableCollection<Topic> GetAllTopicsCollection()
        {
            ObservableCollection<Topic> toRet = new ObservableCollection<Topic>();
            string queryString = "SELECT * FROM topics;";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Topic tp = new Topic();
                tp.Id = int.Parse(row["id"].ToString());
                tp.Name = row["name"].ToString();
                tp.ChapterId = int.Parse(row["chapterId"].ToString());
                toRet.Add(tp);
            }
            return toRet;
        }

        /// <summary>
        /// Find all topics which name contains specifying string
        /// </summary>
        /// <param name="contains">String for searching </param>
        /// <returns>List of finded topics</returns>
        public List<Topic> FindTopicsList(string contains)
        {
            List<Topic> toRet = new List<Topic>();
            string queryString = "SELECT * FROM topics WHERE name LIKE '%" + contains.Replace('\'', '`') + "%';";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Topic tp = new Topic();
                tp.Id = int.Parse(row["id"].ToString());
                tp.Name = row["name"].ToString();
                tp.ChapterId = int.Parse(row["chapterId"].ToString());
                toRet.Add(tp);
            }
            return toRet;
        }

        /// <summary>
        /// Find all topics which name contains specifying string
        /// </summary>
        /// <param name="contains">String for searching </param>
        /// <returns>ObservableCollection of finded topics</returns>
        public ObservableCollection<Topic> FindTopicsCollection(string contains)
        {
            ObservableCollection<Topic> toRet = new ObservableCollection<Topic>();
            string queryString = "SELECT * FROM topics WHERE name LIKE '%" + contains.Replace('\'', '`') + "%';";
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Topic tp = new Topic();
                tp.Id = int.Parse(row["id"].ToString());
                tp.Name = row["name"].ToString();
                tp.ChapterId = int.Parse(row["chapterId"].ToString());
                toRet.Add(tp);
            }
            return toRet;
        }

        /// <summary>
        /// Get all topics for all specialized chapters
        /// </summary>
        /// <param name="chapters">Chapters for getting topics from</param>
        /// <returns>List of finded chapters</returns>
        public List<Topic> GetAllTopicsListForChapters(ICollection<Chapter> chapters)
        {
            List<Topic> toRet = new List<Topic>();
            foreach (Chapter chapter in chapters)
            {
                string queryString = String.Format("SELECT * FROM topics WHERE chapterId = '{0}';", chapter.Id);
                DataTable dt = this.GetData(queryString);
                foreach (DataRow row in dt.Rows)
                {
                    Topic topic = new Topic();
                    topic.Id = int.Parse(row["id"].ToString());
                    topic.Name = row["name"].ToString();
                    topic.ChapterId = int.Parse(row["chapterId"].ToString());
                    toRet.Add(topic);
                }
            }
            return toRet;
        }

        /// <summary>
        /// Get all topics for all specialized chapters
        /// </summary>
        /// <param name="chapters">Chapters for getting topics from</param>
        /// <returns>ObservableCollection of finded chapters</returns>
        public ObservableCollection<Topic> GetAllTopicsCollectionForChapters(ICollection<Chapter> chapters)
        {
            ObservableCollection<Topic> toRet = new ObservableCollection<Topic>();
            foreach (Chapter chapter in chapters)
            {
                string queryString = String.Format("SELECT * FROM topics WHERE chapterId = '{0}';", chapter.Id);
                DataTable dt = this.GetData(queryString);
                foreach (DataRow row in dt.Rows)
                {
                    Topic topic = new Topic();
                    topic.Id = int.Parse(row["id"].ToString());
                    topic.Name = row["name"].ToString();
                    topic.ChapterId = int.Parse(row["chapterId"].ToString());
                    toRet.Add(topic);
                }
            }
            return toRet;
        }

        /// <summary>
        /// Find all topics of specified chapters which name contains specifying string
        /// </summary>
        /// <param name="chapters">Chapter collection to find</param>
        /// <param name="contains">String for searching</param>
        /// <returns>List of finded topics</returns>
        public List<Topic> FindTopicsListForChapters(ICollection<Chapter>chapters, string contains)
        {
            List<Topic> toRet = new List<Topic>();
            foreach (Chapter chapter in chapters)
            {
                string queryString = String.Format("SELECT * FROM topics WHERE chapterId = '{0}' AND name LIKE '%" + contains.Replace('\'', '`') + "%';", chapter.Id);
                DataTable dt = this.GetData(queryString);
                foreach (DataRow row in dt.Rows)
                {
                    Topic topic = new Topic();
                    topic.Id = int.Parse(row["id"].ToString());
                    topic.Name = row["name"].ToString();
                    topic.ChapterId = int.Parse(row["chapterId"].ToString());
                    toRet.Add(topic);
                }
            }
            return toRet;
        }
        
        #endregion //Topics

        #region Questions

        /// <summary>
        /// Add question to database
        /// Id of question will be ignored
        /// </summary>
        /// <param name="name">Name of chapter to add</param>
        public void AddQuestion(Question question)
        {
            //if (this.HasQuestion(question))
            //    throw new Exception("Database allready has such question.");
            string queryString = String.Format("INSERT INTO questions (text, correctAnsIndex,topicId) VALUES ('{0}', {1}, {2});", question.Text.Replace('\'', '`'), question.CorrectAnsIndex, question.TopicId);
            this.ExecuteNonQuery(queryString);
            queryString = String.Format("SELECT id FROM questions WHERE (text = '{0}' AND correctAnsIndex = {1} AND topicId = {2});", question.Text.Replace('\'', '`'), question.CorrectAnsIndex, question.TopicId);
            question.Id = int.Parse(this.ExecuteScalar(queryString));
            foreach (string ans in question.Answers)
            {
                queryString = String.Format("INSERT INTO answers (text, questionId) VALUES ('{0}', {1});", ans.Replace('\'', '`'), question.Id);
                this.ExecuteNonQuery(queryString);
            }
        }

        /// <summary>
        /// Update question data by question Id
        /// </summary>
        /// <param name="question">Question to update</param>
        public void UpdateQuestion(Question question)
        {
            if (!this.HasQuestion(question.Id))
                throw new Exception("There is nothing to update.");
            string queryString = String.Format("UPDATE questions SET text='{0}', correctAnsIndex = {1}, topicId = {2} WHERE id={3};", question.Text.Replace('\'', '`'), question.CorrectAnsIndex, question.TopicId, question.Id);
            this.ExecuteNonQuery(queryString);
            queryString = String.Format("DELETE FROM answers WHERE questionId={0};", question.Id);
            this.ExecuteNonQuery(queryString);
            foreach (string ans in question.Answers)
            {
                queryString = String.Format("INSERT INTO answers (text, questionId) VALUES ('{0}', {1});", ans.Replace('\'', '`'), question.Id);
                this.ExecuteNonQuery(queryString);
            }
        }

        /// <summary>
        /// Delete specified question from database by Id
        /// </summary>
        /// <param name="questionId">Id of question to delete</param>
        public void DeleteQuestion(int questionId)
        {
            if (!this.HasQuestion(questionId))
                throw new Exception("There is no question to delete");
            string queryString = String.Format("DELETE FROM questions WHERE id={0};", questionId);
            this.ExecuteNonQuery(queryString);
        }

        /// <summary>
        /// Delete specified question from database by text and topicId
        /// </summary>
        /// <param name="questionId">Question to delete by text and topicId</param>
        public void DeleteQuestion(Question question)
        {
            if (!this.HasQuestion(question))
                throw new Exception("There is no question to delete");
            string queryString = String.Format("DELETE FROM questions WHERE text='{0}' AND topicId = {1};", question.Text.Replace('\'', '`'), question.TopicId);
            this.ExecuteNonQuery(queryString);
        }

        /// <summary>
        /// Check if question with specified Id exists in database
        /// </summary>
        /// <param name="questionId">Id of specified question</param>
        /// <returns>Bool value that indicates question existing</returns>
        public bool HasQuestion(int questionId)
        {
            string queryString = String.Format("SELECT COUNT(id) FROM questions WHERE id = {0};", questionId);
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Check if question with specified text and topicId exists in database
        /// </summary>
        /// <param name="question">Specified question</param>
        /// <returns>Bool value that indicates question existing</returns>
        public bool HasQuestion(Question question)
        {
            string queryString = String.Format("SELECT COUNT(id) FROM questions WHERE text = '{0}' AND topicId = {1};", question.Text.Replace('\'', '`'), question.TopicId);
            int cnt = int.Parse(this.ExecuteScalar(queryString));
            return cnt > 0;
        }

        /// <summary>
        /// Get specified question by it Id
        /// </summary>
        /// <param name="questionId">Id of specofoed question</param>
        /// <returns>Searched question</returns>
        public Question GetQuestion(int questionId)
        {
            Question toRet = new Question();
            string queryString = String.Format("SELECT * FROM questions WHERE id = {0};", questionId);
            try
            {
                DataTable dt = this.GetData(queryString);
                DataRow row = dt.Rows[0];
                toRet.Id = int.Parse(row["id"].ToString());
                toRet.Text = row["text"].ToString();
                toRet.CorrectAnsIndex = int.Parse(row["correctAnsIndex"].ToString());
                toRet.TopicId = int.Parse(row["topicId"].ToString());
                queryString = String.Format("SELECT * FROM answers WHERE questionId = {0};", questionId);
                List<string> answers = new List<string>();
                dt = this.GetData(queryString);
                foreach (DataRow rw in dt.Rows)
                {
                    string ans = rw["text"].ToString();
                    answers.Add(ans);
                }
                toRet.Answers = answers;
            }
            catch (Exception ex)
            {
                throw new Exception("There are no question with such id.");
            }
            return toRet;
        }

        /// <summary>
        /// Get all questions of specified topic
        /// </summary>
        /// <param name="topicId">Id of specified topic</param>
        /// <returns>List of finded question</returns>
        public List<Question> GetAllTopicsQuestionsList(int topicId)
        {
            List<Question> toRet = new List<Question>();
            string queryString = String.Format("SELECT * FROM questions WHERE topicId = {0};", topicId);
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Question toAdd = new Question();
                toAdd.Id = int.Parse(row["id"].ToString());
                toAdd.Text = row["text"].ToString();
                toAdd.CorrectAnsIndex = int.Parse(row["correctAnsIndex"].ToString());
                toAdd.TopicId = int.Parse(row["topicId"].ToString());
                toRet.Add(toAdd);
            }
            foreach (Question qs in toRet)
            {
                queryString = String.Format("SELECT * FROM answers WHERE questionId = {0};", qs.Id);
                List<string> answers = new List<string>();
                dt = this.GetData(queryString);
                foreach (DataRow rw in dt.Rows)
                {
                    string ans = rw["text"].ToString();
                    answers.Add(ans);
                }
                qs.Answers = answers;
            }
            return toRet;
        }

        /// <summary>
        /// Get all questions of specified topic
        /// </summary>
        /// <param name="topicId">Specified topic</param>
        /// <returns>List of finded question</returns>
        public List<Question> GetAllTopicsQuestionsList(Topic topic)
        {
            return GetAllTopicsQuestionsList(topic.Id);
        }

        /// <summary>
        /// Get all questions of specified topic
        /// </summary>
        /// <param name="topicId">Id of specified topic</param>
        /// <returns>ObservableCollection of finded question</returns>
        public ObservableCollection<Question> GetAllTopicsQuestionsCollection(int topicId)
        {
            ObservableCollection<Question> toRet = new ObservableCollection<Question>();
            string queryString = String.Format("SELECT * FROM questions WHERE topicId = {0};", topicId);
            DataTable dt = this.GetData(queryString);
            foreach (DataRow row in dt.Rows)
            {
                Question toAdd = new Question();
                toAdd.Id = int.Parse(row["id"].ToString());
                toAdd.Text = row["text"].ToString();
                toAdd.CorrectAnsIndex = int.Parse(row["correctAnsIndex"].ToString());
                toAdd.TopicId = int.Parse(row["topicId"].ToString());
                toRet.Add(toAdd);
            }
            foreach (Question qs in toRet)
            {
                queryString = String.Format("SELECT * FROM answers WHERE questionId = {0};", qs.Id);
                List<string> answers = new List<string>();
                dt = this.GetData(queryString);
                foreach (DataRow rw in dt.Rows)
                {
                    string ans = rw["text"].ToString();
                    answers.Add(ans);
                }
                qs.Answers = answers;
            }
            return toRet;
        }

        /// <summary>
        /// Get all questions of specified topic
        /// </summary>
        /// <param name="topicId">Specified topic</param>
        /// <returns>ObservableCollection of finded question</returns>
        public ObservableCollection<Question> GetAllTopicsQuestionsCollection(Topic topic)
        {
            return GetAllTopicsQuestionsCollection(topic.Id);
        }

        /// <summary>
        /// Get all questions for all specified topics from collection
        /// </summary>
        /// <param name="topics">Collection of topics to find questions</param>
        /// <returns>List of all searched questions</returns>
        public List<Question> GetListOfAllQuestionsForTopics(ICollection<Topic> topics)
        {
            List<Question> result = new List<Question>();
            foreach (Topic tp in topics)
            {
                result = result.Concat(this.GetAllTopicsQuestionsList(tp.Id)).ToList();
            }
            return result;
        }

        /// <summary>
        /// Get all questions for all specified topics from collection
        /// </summary>
        /// <param name="topics">Collection of topics to find questions</param>
        /// <returns>ObservableCollection of all searched questions</returns>
        public ObservableCollection<Question> GetCollectionOfAllQuestionsForTopics(ICollection<Topic> topics)
        {
            ObservableCollection<Question> result = new ObservableCollection<Question>();
            foreach (Topic tp in topics)
            {
                result = new ObservableCollection<Question>(result.Concat(this.GetAllTopicsQuestionsCollection(tp.Id)));
            }
            return result;
        }

        /// <summary>
        /// Get specified number of random questions from specified topics
        /// </summary>
        /// <param name="topics">Specified topics</param>
        /// <param name="amount">Number of questions to return</param>
        /// <returns>Random list of questions</returns>
        public List<Question> GetRandomListOfTopicsQuestions(ICollection<Topic> topics, int amount)
        {
            List<Question> result = this.GetListOfAllQuestionsForTopics(topics);
            if (result.Count < amount)
                throw new Exception("All specified topics don`t have sufficient number of questions to return.");
            Random random = new Random();
            while (result.Count > amount)
            {
                int index = random.Next(result.Count);
                result.RemoveAt(index);
            }
            return result;
        }

        /// <summary>
        /// Get specified number of random questions from specified topics
        /// </summary>
        /// <param name="topics">Specified topics</param>
        /// <param name="amount">Number of questions to return</param>
        /// <returns>Random ObservableCollection of questions</returns>
        public ObservableCollection<Question> GetRandomCollectionOfTopicsQuestions(ICollection<Topic> topics, int amount)
        {
            ObservableCollection<Question> result = this.GetCollectionOfAllQuestionsForTopics(topics);
            if (result.Count < amount)
                throw new Exception("All specified topics don`t have sufficient number of questions to return.");
            Random random = new Random();
            while (result.Count > amount)
            {
                int index = random.Next(result.Count);
                result.RemoveAt(index);
            }
            return result;
        }

        #endregion //Questions
    }
}
